﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlServerCe;


namespace Picea.Data
{
    public static class SqlHelper
    {
        public static readonly string PiceaConnection =
            ConfigurationManager.ConnectionStrings["piceaBD"].ConnectionString;

        /// <summary>
        /// Envia um Comando Sql que retorna um DataReader da Base.
        /// Autor: Celso Vinicius do Nascimento
        /// </summary>
        /// <param name="stConnectionString">a String de Connexão</param>
        /// <param name="sqlCommand">O commando a ser executado</param>
        /// <returns>SqlReader</returns>
        public static SqlCeDataReader ExecuteReader(string stConnectionString, string sqlCommand)
        {
            SqlCeCommand cmd = new SqlCeCommand();
            cmd.CommandText = sqlCommand;
            cmd.Connection = new SqlCeConnection(stConnectionString);
            cmd.Connection.Open();

            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }

        /// <summary>
        /// Envia um commando sql e retona uma int com o número de linhas afetadas
        /// Autor: Celso Vinicius do Nascimento
        /// </summary>
        /// <param name="stConnectionString">A String de Conexão</param>
        /// <param name="sqlCommand">o commando non query a ser executado</param>
        /// <returns>int - número de linhas afetadas</returns>
        public static int ExecuteNonQuery(string stConnectionString, string sqlCommand)
        {
            SqlCeConnection conn = new SqlCeConnection(stConnectionString);

            SqlCeCommand command = new SqlCeCommand(sqlCommand, conn);
            //command.Prepare();
            conn.Open();
            command.Prepare();
            return command.ExecuteNonQuery();
        }

        /// <summary>
        /// Envia um comando sql e retorna retorna o escalar deste comando
        /// </summary>
        /// <param name="stConnectionString">string de conexão</param>
        /// <param name="sqlCommand">Comando sql a ser executado</param>
        /// <returns>objeto escalar</returns>
        public static object ExecuteScalar(string stConnectionString, string sqlCommand)
        {
            SqlCeConnection conn = new SqlCeConnection(stConnectionString);

            SqlCeCommand command = new SqlCeCommand(sqlCommand, conn);

            object retornoScalar;

            try
            {
                conn.Open();
                retornoScalar = command.ExecuteScalar();
                conn.Close();
                return retornoScalar;

            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
    }
}
